const mysql = require('mysql2/promise');
const logger = require('../utils/logger');

/**
 * MySQL数据库配置
 */
const dbConfig = {
  host: process.env.MYSQL_HOST || 'localhost',
  port: process.env.MYSQL_PORT || 3306,
  user: process.env.MYSQL_USER || 'root',
  password: process.env.MYSQL_PASSWORD || 'your-password',
  database: process.env.MYSQL_DATABASE || 'stock_melon',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
  enableKeepAlive: true,
  keepAliveInitialDelay: 0
};

/**
 * 创建数据库连接池
 */
const pool = mysql.createPool(dbConfig);

/**
 * 测试数据库连接
 */
async function testConnection() {
  try {
    const connection = await pool.getConnection();
    logger.info('MySQL数据库连接成功', {
      host: dbConfig.host,
      database: dbConfig.database
    });
    connection.release();
    return true;
  } catch (error) {
    logger.error('MySQL数据库连接失败', {
      error: error.message,
      host: dbConfig.host,
      database: dbConfig.database
    });
    return false;
  }
}

/**
 * 执行查询
 * @param {string} sql - SQL语句
 * @param {Array} params - 查询参数
 * @returns {Promise} 查询结果
 */
async function query(sql, params = []) {
  try {
    const [results] = await pool.execute(sql, params);
    return results;
  } catch (error) {
    logger.error('数据库查询失败', {
      error: error.message,
      sql: sql.substring(0, 100)
    });
    throw error;
  }
}

/**
 * 批量插入或更新数据
 * @param {string} table - 表名
 * @param {Array} data - 数据数组
 * @param {Array} updateFields - 需要更新的字段
 * @returns {Promise} 执行结果
 */
async function insertOrUpdate(table, data, updateFields = []) {
  if (!data || data.length === 0) {
    return { affectedRows: 0 };
  }

  const connection = await pool.getConnection();
  try {
    await connection.beginTransaction();

    const keys = Object.keys(data[0]);
    const placeholders = keys.map(() => '?').join(', ');
    
    // 构建批量插入SQL
    const insertSql = `INSERT INTO ${table} (${keys.join(', ')}) VALUES (${placeholders})`;
    
    // 构建ON DUPLICATE KEY UPDATE部分
    let updateClause = '';
    if (updateFields.length > 0) {
      updateClause = ' ON DUPLICATE KEY UPDATE ' + 
        updateFields.map(field => `${field} = VALUES(${field})`).join(', ');
    }
    
    const fullSql = insertSql + updateClause;
    
    let totalAffected = 0;
    for (const row of data) {
      const values = keys.map(key => row[key]);
      const [result] = await connection.execute(fullSql, values);
      totalAffected += result.affectedRows;
    }

    await connection.commit();
    logger.info(`批量数据操作成功`, {
      table,
      records: data.length,
      affectedRows: totalAffected
    });

    return { affectedRows: totalAffected, records: data.length };
  } catch (error) {
    await connection.rollback();
    logger.error('批量数据操作失败', {
      error: error.message,
      table
    });
    throw error;
  } finally {
    connection.release();
  }
}

/**
 * 关闭数据库连接池
 */
async function closePool() {
  try {
    await pool.end();
    logger.info('MySQL数据库连接池已关闭');
  } catch (error) {
    logger.error('关闭数据库连接池失败', { error: error.message });
  }
}

module.exports = {
  pool,
  query,
  insertOrUpdate,
  testConnection,
  closePool
};

